import pandas as pd
import matplotlib.pyplot as plt
from matplotlib import rcParams

# 设置中文字体
rcParams['font.family'] = 'KaiTi'

# 读取 a2.xlsx 文件
df = pd.read_excel(r'C:\Users\Lenovo\PycharmProjects\pythonProject1\a2.xlsx')

# 按照“所有权”分组，计算每组“资产负债率”的平均值和标准差
df_grouped = df.groupby('所有权')['资产负债率'].agg(['mean', 'std']).reset_index()

# 计算年份和LSR的散点图和折线图数据
df_scatter_line = df[['年份', 'LSR']].groupby('年份').mean().reset_index()

# 创造一个图形
fig, ax1 = plt.subplots(figsize=(10, 6))

# 绘制折线图
ax1.plot(df_scatter_line['年份'], df_scatter_line['LSR'], marker='o', color='r', linestyle='-', label='LSR')

# 添加均值水平线
mean_lsr = df['LSR'].mean()
ax1.axhline(mean_lsr, color='b', linestyle='--', label=f'Mean LSR ({mean_lsr:.2f})')

#为x，y轴设定标签，给ax1添加标题
ax1.set_xlabel('年份')
ax1.set_ylabel('LSR')
ax1.set_title('年份与LSR的散点图和折线图')
ax1.legend()


# 调整布局后展示
plt.tight_layout()
plt.show()

# 按照行业代码大类（例如字母C表示所有制造业）分组，计算资产负债率的均值
df['行业代码大类'] = df['行业代码'].apply(lambda x: x[0])

df_industry = df.groupby(['行业代码大类', '年份'])['资产负债率'].mean().reset_index()

# 获取独特年份的数量
years = df_industry['年份'].unique()
num_years = len(years)

# 如果有多个年份，就使用两列来布局子图
rows = (num_years + 1) // 2  # 两列
cols = 2 if num_years > 1 else 1

# 绘制饼图
fig, axs = plt.subplots(rows, cols, figsize=(12, 5 * rows))#指定行数和列数
axs = axs.flatten() if num_years > 1 else [axs]

for i, year in enumerate(years):
    df_year = df_industry[df_industry['年份'] == year]
    labels = df_year['行业代码大类']
    sizes = df_year['资产负债率']#获取数据
    axs[i].pie(sizes, labels=labels, autopct='%1.1f%%', startangle=140)
    axs[i].set_title(f'{year}年行业资产负债率比例')# 设置标题

# 隐藏多余的子图
for i in range(num_years, len(axs)):
    fig.delaxes(axs[i])


plt.show()
